package com.xmy.cultivate.mapper;

import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.baomidou.mybatisplus.core.metadata.IPage;
import com.xmy.cultivate.entity.StudentFaceManyDetail;
import com.xmy.cultivate.entity.views.StudyPlan;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;

import java.util.List;

/**
 * <p>
 * 人脸采集 Mapper 接口
 * </p>
 *
 * @author hpiggy
 * @since 2024-04-19
 */
public interface StudentFaceManyDetailMapper extends BaseMapper<StudentFaceManyDetail> {
    @Select({"<script>",
            "SELECT student_id,subjects_id,grade_id,lesson_type,year_class_id,course_type_id,teacher_name,",

            "<if test= 'type == 6'>",
                "t2.id,t1.school_id,t1.pid AS schoolPid,t1.school_name,t2.year_part,t2.quarter_num,t2.teacher_id,",
                "SUM( CASE WHEN t2.id is not null ",
                "THEN 1 ELSE 0 END ) AS allStudentCount,",
                "<if test= 'enterOrFollow == 1'>",
                    "SUM( CASE WHEN t2.id is not null and is_guanzhu = 1 ",
                    "THEN 1 ELSE 0 END ) AS alreadyShutNum,",
                    "SUM( CASE WHEN t2.id is not null and (is_guanzhu = 0 OR is_guanzhu is null)  ",
                    "THEN 1 ELSE 0 END ) AS notShutNum",
                "</if>",
                "<if test= 'enterOrFollow == 2'>",
                    "SUM( CASE WHEN t2.id is not null and is_gather = 1 ",
                    "THEN 1 ELSE 0 END ) AS alreadyShutNum,",
                    "SUM( CASE WHEN t2.id is not null and (is_gather = 0 OR is_gather is null)  ",
                    "THEN 1 ELSE 0 END ) AS notShutNum ",
                "</if>",
                "<if test= 'enterOrFollow == 3'>",
                    "SUM( CASE WHEN t2.id is not null and id_card_no IS NOT NULL AND id_card_no &lt;&gt; '' ",
                    "THEN 1 ELSE 0 END ) AS alreadyShutNum,",
                    "SUM( CASE WHEN t2.id is not null and (id_card_no IS NULL OR id_card_no = '') ",
                    "THEN 1 ELSE 0 END ) AS notShutNum ",
                "</if>",
            "FROM ",
            "( SELECT * FROM school_quarter WHERE year_part = ${yearPart} AND quarter_num = ${quarterNum} AND deleted = 0) AS t1 ",
            "LEFT JOIN ( ",
            "</if>",

            "<if test= 'type != 6'>",
                "id,school_id,year_part,quarter_num,teacher_id,",
                "getSchoolName(school_id) AS school_name,",
                "count( id ) AS allStudentCount,",
                "<if test= 'enterOrFollow == 1'>",
                    "SUM( CASE WHEN is_guanzhu = 1 THEN 1 ELSE 0 END ) AS alreadyShutNum,",
                    "SUM( CASE WHEN is_guanzhu = 0 OR is_guanzhu is null THEN 1 ELSE 0 END ) AS notShutNum ",
                "</if>",
                "<if test= 'enterOrFollow == 2'>",
                    "SUM( CASE WHEN is_gather = 1 THEN 1 ELSE 0 END ) AS alreadyShutNum,",
                    "SUM( CASE WHEN is_gather = 0 OR is_gather is null THEN 1 ELSE 0 END ) AS notShutNum ",
                "</if>",
                "<if test= 'enterOrFollow == 3'>",
                    "SUM( CASE WHEN id_card_no IS NOT NULL AND id_card_no &lt;&gt; '' THEN 1 ELSE 0 END ) AS alreadyShutNum,",
                    "SUM( CASE WHEN id_card_no IS NULL OR id_card_no = '' THEN 1 ELSE 0 END ) AS notShutNum ",
                "</if>",
                "FROM (",
            "</if>",

            "SELECT t1.*,t2.is_gather,t2.is_guanzhu,t2.id_card_no,t3.residue_course, ",
            "getStaffNameForId ( t1.teacher_id ) AS teacher_name ",
            "FROM ",
            "student_grade_view AS t1 ",
            "INNER JOIN staff AS sff ON sff.id = t1.teacher_id ",
            "LEFT JOIN student AS t2 ON t2.id = t1.student_id ",
            "INNER JOIN order_detail AS o ON t2.id = o.student_id ",
            "INNER JOIN ( ",
            "SELECT ",
            "id,SUM( residue_course_count ) /3 AS residue_course,school_id,subjects_id,student_id ",
            "FROM order_detail",
            "WHERE ",
            "deleted = 0 ",
            "AND year_part = ${yearPart} ",
            "AND quarter_num = ${quarterNum} ",
            "AND `status` &lt;&gt; 2 ",
            "AND lesson_type = 1",
            "GROUP BY",
            "school_id,",
            "student_id,",
            "subjects_id,",
            "lesson_type ",
            ") AS t3 ON o.id = t3.id ",
            "AND t1.student_id = t3.student_id ",
            "AND t1.subjects_id = t3.subjects_id ",
            "WHERE ",
            "t1.year_part = ${yearPart} ",
            "AND t1.quarter_num = ${quarterNum} ",
            "AND t1.is_last = 1 ",
            "AND t1.is_makeup = 0 ",
            "AND t1.deleted = 0 ",
            "AND t1.STATUS = 1 ",
            "AND t1.lesson_type = 1 ",
            "AND t1.reading_status = 1 AND t3.residue_course &gt; 0 ",
            "<when test='schoolId!=null and schoolId!=&apos;&apos;'>",
                "AND t1.school_id in (${schoolId}) ",
            "</when>",
            "<if test= 'type == 3'>",
                "AND t1.teacher_id in(select admin_id from role_admin where role_id = 1568160887369994242) ",
            "</if>",
            "GROUP BY ",
            "t1.school_id,",
            "t1.student_id,",
            "t1.subjects_id,",
            "t1.teacher_id ",
            "<if test= 'type != 6 '>",
                ") AS t ",
            "</if>",
            "<if test= 'type == 6 '>",
                ") AS t2 ON t1.school_id = t2.school_id ",
            "</if>",
            "GROUP BY ",
            "<if test= 'type == 1 '>",
                "teacher_id ",
            "</if>",
            "<if test= 'type == 2 '>",
                "teacher_id,school_id ",
            "</if>",
            "<if test= 'type == 3 '>",
                "teacher_id ",
            "</if>",
            "<if test= 'type == 4 or type == 6'>",
                "school_id ",
            "</if>",
            "</script>"})
    public abstract List<StudyPlan> getStudyPlanList(@Param("yearPart") String yearPart, @Param("quarterNum") String quarterNum, @Param("type") String type, @Param("enterOrFollow") String enterOrFollow, @Param("schoolId") String schoolId);

    @Select({"<script>",
            "SELECT t1.*,t2.is_gather,t2.is_guanzhu,t2.id_card_no, ",
            "getSchoolName(t1.school_id) AS school_name,",
            "getStaffNameForId ( t1.teacher_id ) AS teacher_name,t3.residue_course, ",
            "CASE ",
                "WHEN (t2.id_card_no IS NOT NULL AND t2.id_card_no &lt;&gt; '') THEN 1 ",
                "WHEN (t2.id_card_no IS NULL OR t2.id_card_no = '') THEN 2 ",
            "END AS card ",
            "FROM ",
            "student_grade_view AS t1 ",
            "INNER JOIN staff AS sff ON sff.id = t1.teacher_id ",
            "LEFT JOIN student AS t2 ON t2.id = t1.student_id ",
            "INNER JOIN order_detail AS o ON t2.id = o.student_id ",
            "INNER JOIN ( ",
            "SELECT ",
            "id,SUM( residue_course_count ) /3 AS residue_course,school_id,subjects_id,student_id ",
            "FROM order_detail",
            "WHERE ",
            "deleted = 0 ",
            "AND year_part = ${yearPart} ",
            "AND quarter_num = ${quarterNum} ",
            "AND `status` &lt;&gt; 2 ",
            "AND lesson_type = 1",
            "GROUP BY",
            "school_id,",
            "student_id,",
            "subjects_id,",
            "lesson_type ",
            ") AS t3 ON o.id = t3.id ",
            "AND t1.student_id = t3.student_id ",
            "AND t1.subjects_id = t3.subjects_id ",
            "WHERE ",
            "t1.year_part = ${yearPart} ",
            "AND t1.quarter_num = ${quarterNum} ",
            "AND t1.is_last = 1 ",
            "AND t1.is_makeup = 0 ",
            "AND t1.deleted = 0 ",
            "AND t1.STATUS = 1 ",
            "AND t1.lesson_type = 1 ",
            "AND t1.reading_status = 1 AND t3.residue_course &gt; 0 ",
            "<when test='gradeId!=null and gradeId!=&apos;&apos;'>",
                "AND t1.grade_id in (${gradeId}) ",
            "</when>",
            "<when test='schoolId!=null and schoolId!=&apos;&apos;'>",
                "AND t1.school_id in (${schoolId}) ",
            "</when>",
            "<when test='teacherId!=null and teacherId!=&apos;&apos;'>",
                "AND t1.teacher_id = ${teacherId} ",
            "</when>",
            "<if test= 'enterOrFollow == 1'>",
                "<if test= 'enterOrFollowState == 0'>",
                    "AND (t2.is_guanzhu = 0 OR t2.is_guanzhu is null)",
                "</if>",
                "<if test= 'enterOrFollowState == 1'>",
                    "AND t2.is_guanzhu = 1 ",
                "</if>",
            "</if>",
            "<if test= 'enterOrFollow == 2'>",
                "<if test= 'enterOrFollowState == 0'>",
                    "AND (t2.is_gather = 0 OR t2.is_gather is null)",
                "</if>",
                "<if test= 'enterOrFollowState == 1'>",
                    "AND t2.is_gather = 1 ",
                "</if>",
            "</if>",
            "<if test= 'enterOrFollow == 3'>",
                "<if test= 'enterOrFollowState == 0'>",
                    "AND (t2.id_card_no IS NULL OR t2.id_card_no = '' )",
                "</if>",
                "<if test= 'enterOrFollowState == 1'>",
                    "AND (t2.id_card_no IS NOT NULL AND t2.id_card_no &lt;&gt; '' )",
                "</if>",
            "</if>",
            "GROUP BY ",
            "t1.school_id,",
            "t1.student_id,",
            "t1.subjects_id,",
            "t1.teacher_id ",
            "</script>"})
    public abstract IPage<StudyPlan> getStudyPlanDetails(IPage<?> page, @Param("yearPart") String yearPart, @Param("quarterNum") String quarterNum, @Param("teacherId") String teacherId, @Param("enterOrFollow") String enterOrFollow, @Param("enterOrFollowState") String enterOrFollowState, @Param("gradeId") String gradeId, @Param("schoolId") String schoolId);

    @Select({"<script>",
            "SELECT student_id,subjects_id,grade_id,lesson_type,year_class_id,course_type_id,getStaffNameForId ( t1.teacher_id ) AS teacher_name,school_id,year_part,quarter_num,t1.teacher_id,team_id,t1.team_name,t1.team_pid,",
            "getSchoolName(school_id) AS school_name,",
            "SUM( CASE WHEN t.id is not null ",
            "THEN 1 ELSE 0 END ) AS allStudentCount,",
            "<if test= 'enterOrFollow == 1'>",
            "SUM( CASE WHEN t.id is not null and is_guanzhu = 1 ",
            "THEN 1 ELSE 0 END ) AS alreadyShutNum,",
            "SUM( CASE WHEN t.id is not null and (is_guanzhu = 0 OR is_guanzhu is null)  ",
            "THEN 1 ELSE 0 END ) AS notShutNum",
            "</if>",
            "<if test= 'enterOrFollow == 2'>",
            "SUM( CASE WHEN t.id is not null and is_gather = 1 ",
            "THEN 1 ELSE 0 END ) AS alreadyShutNum,",
            "SUM( CASE WHEN t.id is not null and (is_gather = 0 OR is_gather is null)  ",
            "THEN 1 ELSE 0 END ) AS notShutNum ",
            "</if>",
            "<if test= 'enterOrFollow == 3'>",
            "SUM( CASE WHEN t.id is not null and id_card_no IS NOT NULL AND id_card_no &lt;&gt; '' ",
            "THEN 1 ELSE 0 END ) AS alreadyShutNum,",
            "SUM( CASE WHEN t.id is not null and (id_card_no IS NULL OR id_card_no = '') ",
            "THEN 1 ELSE 0 END ) AS notShutNum ",
            "</if>",
            "FROM",
            "( ",
            "SELECT ",
            "td.*,td.sourse_id as teacher_id ,tm.team_name,tm.id AS team_id,tm.pid AS team_pid ",
            "FROM ",
            "track_market AS tm ",
            "LEFT JOIN track_detail AS td ON tm.id = td.track_id ",
            "AND tm.deleted = td.deleted ",
            "WHERE ",
            "tm.classify = ${classify} ",
            "AND tm.track_type = ${trackType} ",
            "AND tm.`status` = 1 ",
            "AND tm.deleted = 0 ",
            "AND tm.year_part = ${yearPart} ",
            "AND tm.quarter_num = ${quarterNum} ",
            "<when test='teamId!=null and teamId!=&apos;&apos; '>",
                "and tm.id IN (${teamId}) ",
            "</when>",
            "GROUP BY ",
            "tm.id,td.sourse_id ",
            ") AS t1 ",
            "LEFT JOIN (",
            "SELECT t1.*,t2.is_gather,t2.is_guanzhu,t2.id_card_no,t3.residue_course",
            "FROM ",
            "student_grade_view AS t1 ",
            "INNER JOIN staff AS sff ON sff.id = t1.teacher_id ",
            "LEFT JOIN student AS t2 ON t2.id = t1.student_id ",
            "INNER JOIN order_detail AS o ON t2.id = o.student_id ",
            "INNER JOIN ( ",
            "SELECT ",
            "id,SUM( residue_course_count ) /3 AS residue_course,school_id,subjects_id,student_id ",
            "FROM order_detail",
            "WHERE ",
            "deleted = 0 ",
            "AND year_part = ${yearPart} ",
            "AND quarter_num = ${quarterNum} ",
            "AND `status` &lt;&gt; 2 ",
            "AND lesson_type = 1",
            "GROUP BY",
            "school_id,",
            "student_id,",
            "subjects_id,",
            "lesson_type ",
            ") AS t3 ON o.id = t3.id ",
            "AND t1.student_id = t3.student_id ",
            "AND t1.subjects_id = t3.subjects_id ",
            "WHERE ",
            "t1.year_part = ${yearPart} ",
            "AND t1.quarter_num = ${quarterNum} ",
            "AND t1.is_last = 1 ",
            "AND t1.is_makeup = 0 ",
            "AND t1.deleted = 0 ",
            "AND t1.STATUS = 1 ",
            "AND t1.lesson_type = 1 ",
            "AND t1.reading_status = 1 AND t3.residue_course &gt; 0 ",
            "<when test='schoolId!=null and schoolId!=&apos;&apos;'>",
                "AND t1.school_id in (${schoolId}) ",
            "</when>",
            "GROUP BY ",
            "t1.school_id,",
            "t1.student_id,",
            "t1.subjects_id,",
            "t1.teacher_id ",
            ") AS t ON t.teacher_id = t1.teacher_id ",
            "AND ( find_in_set( t.year_class_id, t1.year_class_id_str ) OR t1.year_class_id_str = '' ) ",
            "AND ( find_in_set( t.school_id, t1.school_id_str ) OR t1.school_id_str = '' ) ",
            "GROUP BY ",
            "team_id ",
            "<when test='teamId!=null and teamId!=&apos;&apos; '>",
                ",t.teacher_id ",
            "</when>",
            "</script>"})
    public abstract List<StudyPlan> getStudyPlanTeamList(@Param("yearPart") String yearPart, @Param("quarterNum") String quarterNum, @Param("enterOrFollow") String enterOrFollow, @Param("schoolId") String schoolId,@Param("classify") String classify,@Param("trackType") String trackType,@Param("teamId") String teamId);

    @Select({"<script>",
            "SELECT * FROM (",
            "SELECT ",
            "td.*,td.sourse_id as teacher_id ,tm.team_name,tm.id AS team_id,tm.pid AS team_pid ",
            "FROM ",
            "track_market AS tm ",
            "LEFT JOIN track_detail AS td ON tm.id = td.track_id ",
            "AND tm.deleted = td.deleted ",
            "WHERE ",
            "tm.classify = ${classify} ",
            "AND tm.track_type = ${trackType} ",
            "AND tm.`status` = 1 ",
            "AND tm.deleted = 0 ",
            "AND tm.year_part = ${yearPart} ",
            "AND tm.quarter_num = ${quarterNum} ",
            "and tm.id IN (${teamId}) ",
            "GROUP BY ",
            "tm.id,td.sourse_id ",
            ") AS t1 ",
            "INNER JOIN (",
            "SELECT t1.*,t2.is_gather,t2.is_guanzhu,t2.id_card_no, ",
            "getSchoolName(t1.school_id) AS school_name,",
            "getStaffNameForId ( t1.teacher_id ) AS teacher_name,t3.residue_course, ",
            "CASE ",
            "WHEN (t2.id_card_no IS NOT NULL AND t2.id_card_no &lt;&gt; '') THEN 1 ",
            "WHEN (t2.id_card_no IS NULL OR t2.id_card_no = '') THEN 2 ",
            "END AS card ",
            "FROM ",
            "student_grade_view AS t1 ",
            "INNER JOIN staff AS sff ON sff.id = t1.teacher_id ",
            "LEFT JOIN student AS t2 ON t2.id = t1.student_id ",
            "INNER JOIN order_detail AS o ON t2.id = o.student_id ",
            "INNER JOIN ( ",
            "SELECT ",
            "id,SUM( residue_course_count ) /3 AS residue_course,school_id,subjects_id,student_id ",
            "FROM order_detail",
            "WHERE ",
            "deleted = 0 ",
            "AND year_part = ${yearPart} ",
            "AND quarter_num = ${quarterNum} ",
            "AND `status` &lt;&gt; 2 ",
            "AND lesson_type = 1",
            "GROUP BY",
            "school_id,",
            "student_id,",
            "subjects_id,",
            "lesson_type ",
            ") AS t3 ON o.id = t3.id ",
            "AND t1.student_id = t3.student_id ",
            "AND t1.subjects_id = t3.subjects_id ",
            "WHERE ",
            "t1.year_part = ${yearPart} ",
            "AND t1.quarter_num = ${quarterNum} ",
            "AND t1.is_last = 1 ",
            "AND t1.is_makeup = 0 ",
            "AND t1.deleted = 0 ",
            "AND t1.STATUS = 1 ",
            "AND t1.lesson_type = 1 ",
            "AND t1.reading_status = 1 AND t3.residue_course &gt; 0 ",
            "<when test='gradeId!=null and gradeId!=&apos;&apos;'>",
                "AND t1.grade_id in (${gradeId}) ",
            "</when>",
            "<when test='schoolId!=null and schoolId!=&apos;&apos;'>",
                "AND t1.school_id in (${schoolId}) ",
            "</when>",
            "<when test='teacherId!=null and teacherId!=&apos;&apos;'>",
                "AND t1.teacher_id = ${teacherId} ",
            "</when>",
            "<if test= 'enterOrFollow == 1'>",
                "<if test= 'enterOrFollowState == 0'>",
                    "AND (t2.is_guanzhu = 0 OR t2.is_guanzhu is null)",
                "</if>",
                "<if test= 'enterOrFollowState == 1'>",
                    "AND t2.is_guanzhu = 1 ",
                "</if>",
            "</if>",
            "<if test= 'enterOrFollow == 2'>",
                "<if test= 'enterOrFollowState == 0'>",
                    "AND (t2.is_gather = 0 OR t2.is_gather is null)",
                "</if>",
                "<if test= 'enterOrFollowState == 1'>",
                    "AND t2.is_gather = 1 ",
                "</if>",
            "</if>",
            "<if test= 'enterOrFollow == 3'>",
                "<if test= 'enterOrFollowState == 0'>",
                    "AND (t2.id_card_no IS NULL OR t2.id_card_no = '' )",
                "</if>",
                "<if test= 'enterOrFollowState == 1'>",
                    "AND (t2.id_card_no IS NOT NULL AND t2.id_card_no &lt;&gt; '' )",
                "</if>",
            "</if>",
            "GROUP BY ",
            "t1.school_id,",
            "t1.student_id,",
            "t1.subjects_id,",
            "t1.teacher_id ",
            ") AS t ON t.teacher_id = t1.teacher_id ",
            "AND ( find_in_set( t.year_class_id, t1.year_class_id_str ) OR t1.year_class_id_str = '' ) ",
            "AND ( find_in_set( t.school_id, t1.school_id_str ) OR t1.school_id_str = '' ) ",
            "</script>"})
    public abstract IPage<StudyPlan> getStudyPlanTeamDetails(IPage<?> page, @Param("yearPart") String yearPart, @Param("quarterNum") String quarterNum, @Param("teacherId") String teacherId, @Param("enterOrFollow") String enterOrFollow, @Param("enterOrFollowState") String enterOrFollowState, @Param("gradeId") String gradeId, @Param("schoolId") String schoolId,@Param("classify") String classify,@Param("trackType") String trackType,@Param("teamId") String teamId);

}
